﻿CREATE VIEW dbo.v_BuyersOrder_10
AS
SELECT     LI.LoanID, D.ID AS DealerID, L.DealerSalesPersonID, L.ContractHolderID, L.StatusID, S_L.Name AS Status, VS.StatusID AS VerificationStatusID, 
                      S_VS.Name AS Status_Verification, CI.Price AS SalesPrice, CI.VIN, CI.BookValue, CI.BookValueDesc, CI.ModelAge, CI.Mileage, 
                      CI.Mileage / CI.ModelAge AS MilesPerYear, LI.SalesTaxPercent, ISNULL(NULLIF (ST_D.Tax, 0), ST_CH.Tax) AS SalesTaxPercentByState, 
                      LI.SalesTaxAmount, dbo.ShowFlag(LI.SalesTaxPercent) AS Lamp_SalesTaxPercent, dbo.ShowFlag(LI.SalesTaxAmount) AS Lamp_SalesTaxAmount, 
                      LI.DownPayment, LI.DealerFee, LI.TaxableFees, LI.TradeIn, dbo.SumNum5(CI.Price, LI.DealerFee, LI.TaxableFees, - LI.TradeIn, 0) AS UnpaidBalance, 
                      LI.UnPaidBalanceWithTax, dbo.ShowFlag(LI.UnPaidBalanceWithTax) AS Lamp_UnPaidBalanceWithTax, LI.GAP, LI.ServiceContract, 
                      LI.OtherFees AS NonTaxableFees, LI.TagFee, LI.APROverride, LI.NumberOfPayments AS LoanTerm, LI.ContractDate, LI.FirstPaymentDate, 
                      LI.AddOnRateOverride, LI.PaymentOverride, LI.NumberOfPayments, LI.PaymentsPerYear, LI.IsRule78, CI.IsSUV, CI.TrackerTypeID, CI.IsIndependent, 
                      LI.PaymentTypeID, LI.InterestType, LI.DayCountConventionID, LI.PrincipalBalance, CI.BookValueCurrent, CI.BookValueCurrentTypeID, 
                      D.State AS DealerState, CH.State AS ContractHolderState, CI.Year AS CarYear, L.GUID_Documents, dbo.v_Repossession.StatusID AS StatusID_Repo, 
                      dbo.v_Repossession.StatusDate AS StatusDate_Repo, dbo.v_Repossession.StatusUser AS StatusUser_Repo, 
                      dbo.v_Repossession.Status AS Status_Repo, BK.StatusID AS StatusID_BK, BK.StatusDate AS StatusDate_BK, BK.StatusUser AS StatusUser_BK
, CI.BookValueCurrentDate, CI.BookValueProviderCode

FROM         dbo.t_Loan AS L INNER JOIN
                      dbo.t_LoanInfo AS LI ON LI.LoanID = L.ID INNER JOIN
                      dbo.t_VerificationSmart AS VS ON VS.LoanID = L.ID INNER JOIN
                      dbo.t_Dealer_SalesPersons AS D_SP ON L.DealerSalesPersonID = D_SP.ID INNER JOIN
                      dbo.t_Dealer AS D ON D.ID = D_SP.DealerID INNER JOIN
                      dbo.v_CarInfo AS CI ON CI.LoanID = L.ID INNER JOIN
                      dbo.ts_SalesTax AS ST_D ON D.State = ST_D.State INNER JOIN
                      dbo.t_State AS S_L ON L.StatusID = S_L.ID INNER JOIN
                      dbo.t_State AS S_VS ON VS.StatusID = S_VS.ID INNER JOIN
                      dbo.t_ContractHolder AS CH ON L.ContractHolderID = CH.ID INNER JOIN
                      dbo.ts_SalesTax AS ST_CH ON CH.State = ST_CH.State INNER JOIN
                      dbo.v_Repossession ON L.ID = dbo.v_Repossession.LoanID INNER JOIN
                      dbo.t_Bankruptcy AS BK ON L.ID = BK.LoanID


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[28] 4[32] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[49] 4[25] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[37] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 1
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "L"
            Begin Extent = 
               Top = 49
               Left = 180
               Bottom = 408
               Right = 360
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LI"
            Begin Extent = 
               Top = 34
               Left = 551
               Bottom = 234
               Right = 752
            End
            DisplayFlags = 344
            TopColumn = 27
         End
         Begin Table = "VS"
            Begin Extent = 
               Top = 87
               Left = 545
               Bottom = 386
               Right = 715
            End
            DisplayFlags = 344
            TopColumn = 17
         End
         Begin Table = "D_SP"
            Begin Extent = 
               Top = 141
               Left = 544
               Bottom = 250
               Right = 695
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "D"
            Begin Extent = 
               Top = 150
               Left = 723
               Bottom = 295
               Right = 881
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "CI"
            Begin Extent = 
               Top = 289
               Left = 516
               Bottom = 521
               Right = 710
            End
            DisplayFlags = 344
            TopColumn = 9
         End
         Begin Table = "ST_D"
            Begin Extent = 
               Top = 204
               Left = 543
               Bottom = 283
               Right = 694
            End
            DisplayFlags = 344
            TopColumn = 0', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_BuyersOrder_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'End
         Begin Table = "S_L"
            Begin Extent = 
               Top = 48
               Left = 0
               Bottom = 340
               Right = 151
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "S_VS"
            Begin Extent = 
               Top = 91
               Left = 734
               Bottom = 242
               Right = 885
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "CH"
            Begin Extent = 
               Top = 375
               Left = 535
               Bottom = 642
               Right = 691
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "ST_CH"
            Begin Extent = 
               Top = 457
               Left = 336
               Bottom = 535
               Right = 487
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "v_Repossession"
            Begin Extent = 
               Top = 250
               Left = 694
               Bottom = 398
               Right = 845
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "BK"
            Begin Extent = 
               Top = 331
               Left = 728
               Bottom = 495
               Right = 883
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
      PaneHidden = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 61
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 7170
         Alias = 3060
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_BuyersOrder_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_BuyersOrder_10';

